package connection;

import java.sql.*;
//import com.mysql.jdbc.*;
import com.mysql.jdbc.exceptions.jdbc4.*;
import bean.*;

import java.util.ArrayList;
import java.util.Iterator;

public class DatabaseConnection {

	Connection con = null;
	static ResultSet rs,rs1,rs2;
	Statement stmt = null;
	
	public DatabaseConnection() {
		try {
		
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/SimpleMarketPlace", "root", "root");
			stmt = con.createStatement();
			//System.out.println("harry");
			if(!con.isClosed())
				System.out.println("Successfully Connected!!!");

		
		}catch(SQLException e) {
			System.out.println(e);
		}
		catch(InstantiationException e) {
			System.out.println(e);
		}
		catch(IllegalAccessException e) {
			System.out.println(e);
		}
		catch(ClassNotFoundException e) {
			System.out.println(e);
			e.printStackTrace();

		}
		
	}
	
	String addUser(String emailId, String password, String firstName, String lastName) {
		
		String result = null;
		int rowCount = 0;
		
		try {
			String query = "Insert into tbl_user (emailId, password, firstName, lastName, lastLoginTime) " +
							"values ('" + emailId + "', MD5('" + password + "'), '" + firstName + "', '" + lastName + "', NOW())";
			System.out.println(query);
			rowCount = stmt.executeUpdate(query);
			if(rowCount == 1){
				result="true";
				
				query = "SELECT userId FROM tbl_user WHERE emailId = '" + emailId +"'";
				rs = stmt.executeQuery(query);
				
				
				if(rs.next()) {
					//result = rs.getString("userId");
				}	
				
				System.out.println("Insert Successful");
			}
			else{				
				result="false";							
			}	
		
		}
		catch(MySQLIntegrityConstraintViolationException e) {
			result="User with Email Id already exists!";
		}
		catch(SQLException e) {
			result="false";	
			e.printStackTrace();
		}
		
		return result;
		
	}
	
	String retreiveUser(String emailId, String password) {
		String result = null;
		
		
		try {
			String query = "SELECT userId,firstName, lastName, lastLoginTime FROM tbl_user WHERE emailId = '" + emailId +"' AND password = MD5('" + password + "')";
			rs = stmt.executeQuery(query);
			
			
			if(rs.next()) {
				String userId = rs.getString("userId");
				String firstName = rs.getString("firstName");
				String lastName = rs.getString("lastName");
				String lastLoginTime = rs.getString("lastLoginTime");
				System.out.println(lastLoginTime);
				result = userId + "#" + emailId + "#" + firstName + "#" + lastName + "#" + lastLoginTime ;
				
				query = "Update tbl_user SET lastLoginTime = NOW() WHERE userId = '" + userId +"'";
				stmt.executeUpdate(query);
				//result="true";
			}
			else {
				result="false";
			}
		
		}catch(SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	String addAdvertisement(String userId, String itemName, String itemDescription, String itemPrice, String itemQuantity, String sellerInfo) {
		String result = null;
		int rowCount = 0;
		try {
			String query = "Insert into tbl_advertisement (itemName, itemDescription,itemPrice,itemQuantity,sellerInfo,userId) " +
							"values ('" + itemName + "','" + itemDescription + "', '" + itemPrice + "', '" + itemQuantity + "', " +
									"'" + sellerInfo + "','" + userId + "')";
			System.out.println(query);
			rowCount = stmt.executeUpdate(query);
			if(rowCount == 1){
				result="true";
				System.out.println("Insert Successful");
			}
			else{				
				result="false";							
			}	
		
		}		
		catch(SQLException e) {
			result="false";	
			e.printStackTrace();
		}
		
		return result;
	}
	
	ArrayList<Advertisement> fetchAdvertisementById(String userId) {
		
		String query = null;
		ArrayList<Advertisement> alAdvert = new ArrayList<Advertisement>();
		//Advertisement aAdvert[] = new Advertisement[];		
		try {
			query = "SELECT * from tbl_advertisement where userId = '" + userId +"'";
			rs = stmt.executeQuery(query);
			
			while(rs.next()) {
				Advertisement oAdvert = new Advertisement();
				oAdvert.setItemId(rs.getInt("itemId"));
				oAdvert.setItemName(rs.getString("itemName"));
				oAdvert.setItemDescription(rs.getString("itemDescription"));
				oAdvert.setSellerInfo(rs.getString("sellerInfo"));
				oAdvert.setItemQuantity(rs.getInt("itemQuantity"));
				oAdvert.setItemPrice(rs.getFloat("itemPrice"));
				oAdvert.setUserId(rs.getInt("userId"));
				alAdvert.add(oAdvert);				
			}			
			System.out.println(alAdvert.size());
		}
		catch(SQLException e) {
			
		}
		
		
		return alAdvert;
	}
	
	ArrayList<Advertisement> fetchAdvertisementExcludingId(String userId) {
		
		String query = null;
		ArrayList<Advertisement> alAdvert = new ArrayList<Advertisement>();
		
		try {
			query = "SELECT * from tbl_advertisement where userId != '" + userId +"' and itemQuantity != '0'";
			rs = stmt.executeQuery(query);
			
			while(rs.next()) {
				System.out.println("has elements");
				Advertisement oAdvert = new Advertisement();
				oAdvert.setItemId(rs.getInt("itemId"));
				oAdvert.setItemName(rs.getString("itemName"));
				oAdvert.setItemDescription(rs.getString("itemDescription"));
				oAdvert.setSellerInfo(rs.getString("sellerInfo"));
				oAdvert.setItemQuantity(rs.getInt("itemQuantity"));
				oAdvert.setItemPrice(rs.getFloat("itemPrice"));
				oAdvert.setUserId(rs.getInt("userId"));
				alAdvert.add(oAdvert);				
			}			
			
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		
		return alAdvert;
	}
	
	ArrayList<Advertisement> getOtherAdvertisementExcludingId(String userId) {
		
		String query = null;
		ArrayList<Advertisement> alAdvert = new ArrayList<Advertisement>();
		
		try {
			query = "SELECT * from tbl_advertisement where userId != '" + userId +"'";
			rs = stmt.executeQuery(query);
			
			while(rs.next()) {
				System.out.println("has elements");
				Advertisement oAdvert = new Advertisement();
				oAdvert.setItemId(rs.getInt("itemId"));
				oAdvert.setItemName(rs.getString("itemName"));
				oAdvert.setItemDescription(rs.getString("itemDescription"));
				oAdvert.setSellerInfo(rs.getString("sellerInfo"));
				oAdvert.setItemQuantity(rs.getInt("itemQuantity"));
				oAdvert.setItemPrice(rs.getFloat("itemPrice"));
				oAdvert.setUserId(rs.getInt("userId"));
				alAdvert.add(oAdvert);				
			}			
			
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		
		return alAdvert;
	}
	
	String insertCartItem(String userId,String itemId, String itemQuantity) {
		String result = null, query = null;
		
		query = "INSERT into tbl_cart_item (itemId,itemQuantity,userId) VALUES ('" + itemId + "','" + itemQuantity + "','" + userId + "')";
		
		System.out.println(query);
		try {
			int rowCount = stmt.executeUpdate(query);
			if(rowCount == 1){
				result="true";
				System.out.println("Insert Successful");
			}
			else{				
				result="false";							
			}	
		
		}		
		catch(SQLException e) {
			result="false";	
			e.printStackTrace();
		}
	

		
		return result;
	}
	
	ArrayList<CartItem> fetchUserCart(String userId) {
		
		String query = null;
		ArrayList<CartItem> alCartItem = new ArrayList<CartItem>();
		
		try {
			query = "SELECT * from tbl_cart_item where userId = '" + userId +"'";
			rs = stmt.executeQuery(query);
			
			while(rs.next()) {
				CartItem oCartItem = new CartItem();
				oCartItem.setCartItemId(rs.getInt("cart_itemId"));
				oCartItem.setItemId(rs.getInt("itemId"));
				
				oCartItem.setItemQuantity(rs.getInt("itemQuantity"));
				
				oCartItem.setUserId(rs.getInt("userId"));
				alCartItem.add(oCartItem);				
			}			
			
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		
		return alCartItem;
	}
	
	String updateCartItem(String cartItemId, String orderedQuantity) {
		String result = null, query = null;
		
		query = "UPDATE tbl_cart_item SET itemQuantity = '" + orderedQuantity + "' WHERE cart_itemID = '" + cartItemId + "'";
		
		System.out.println(query);
		try {
			int rowCount = stmt.executeUpdate(query);
			if(rowCount == 1){
				result="true";
				System.out.println("update Successful");
			}
			else{				
				result="false";							
			}	
		
		}		
		catch(SQLException e) {
			result="false";	
			e.printStackTrace();
		}
	

		
		return result;
	}
	
	ArrayList<Advertisement> fetchAdvertisementDetailsOfCartItems(String userId) {
		
		String query = null;
		ArrayList<Advertisement> alAdvert = new ArrayList<Advertisement>();
		
		try {
			query = "SELECT * from tbl_advertisement a,tbl_cart_item c where a.itemId = c.itemId and c.userId = '" + userId +"'";
			rs = stmt.executeQuery(query);
			
			while(rs.next()) {
				Advertisement oAdvert = new Advertisement();
				oAdvert.setItemId(rs.getInt("itemId"));
				oAdvert.setItemName(rs.getString("itemName"));
				oAdvert.setItemDescription(rs.getString("itemDescription"));
				oAdvert.setSellerInfo(rs.getString("sellerInfo"));
				oAdvert.setItemQuantity(rs.getInt("itemQuantity"));
				oAdvert.setItemPrice(rs.getFloat("itemPrice"));
				oAdvert.setUserId(rs.getInt("userId"));
				alAdvert.add(oAdvert);				
			}			
			
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		
		return alAdvert;
	}
	
	String deleteItemInCart(String cartItemId) {
		String result = "false", query;
		
		query = "DELETE from tbl_cart_item WHERE cart_itemId = '" + cartItemId+ "'";
		
		System.out.println(query);
		try {
			int rowCount = stmt.executeUpdate(query);
			if(rowCount == 1){
				result="true";
				System.out.println("update Successful");
			}		
		}		
		catch(SQLException e) {
			result="false";	
			e.printStackTrace();
		}
		
		return result;
	}
	
	String checkAvailableQuantityForItemsInCart(String userId) {
		String result = "false",query;
		
		query = "SELECT count(c.cart_itemId) as count_cart_itemId FROM tbl_advertisement a,tbl_cart_item c " +
					"where a.itemId = c.itemId and c.userId = '" + userId +"' and a.itemQuantity < c.itemQuantity";
		try {
				rs = stmt.executeQuery(query);
				rs.next();
				
				if(rs.getInt("count_cart_itemId") == 0) {
					result = "true";

				}			
			}catch(SQLException e) {
				e.printStackTrace();
			}
			
		return result;
	}
	
	String purchaseItemsInCart(String userId) {
		String result = "false";
		ArrayList<CartItem> alCartItem = new ArrayList<CartItem>();
		String query = null;
		
		try {
			query = "SELECT * from tbl_cart_item where userId = '" + userId +"'";
			rs = stmt.executeQuery(query);
			
			while(rs.next()) {
				CartItem oCartItem = new CartItem();
				oCartItem.setCartItemId(rs.getInt("cart_itemId"));
				oCartItem.setItemId(rs.getInt("itemId"));
				
				oCartItem.setItemQuantity(rs.getInt("itemQuantity"));
				
				oCartItem.setUserId(rs.getInt("userId"));
				alCartItem.add(oCartItem);				
			}	
			
			Iterator<CartItem> itr = alCartItem.iterator(); 
			
			while(itr.hasNext()) {
				CartItem oCartItem = (CartItem)itr.next();
				
				
					
				int cartItemId = oCartItem.getCartItemId();
				int itemId = oCartItem.getItemId();
				int orderedQuantity = oCartItem.getItemQuantity();
				String buyerId = userId;
				
				query = "SELECT itemQuantity, itemPrice, userId from tbl_advertisement where itemId = '" + itemId +"'";
				rs1 = stmt.executeQuery(query);
				
				rs1.next();
				
				String sellerId = "" + rs1.getInt("userId");
				int availableQuantity = rs1.getInt("itemQuantity");
				float itemPrice = rs1.getFloat("itemPrice");
				float amount = itemPrice*orderedQuantity;
				int transactionId;
				query = "INSERT INTO tbl_transaction (buyer_userId,seller_userId, amount, time) VALUES ('" + buyerId+ "','" + sellerId+ "', '" + amount+ "',NOW())";
				
				int rowCount = stmt.executeUpdate(query);
				
				if(rowCount == 1) {
					query = "Select transactionId from tbl_transaction order by transactionId DESC LIMIT 1";
					rs2 = stmt.executeQuery(query);
					rs2.next();
					transactionId = rs2.getInt("transactionId");
					
					query = "INSERT INTO tbl_transacted_item (userId,itemId,itemQuantity,itemPrice, transactionId, transactionType) " +
							"VALUES ('" + buyerId+ "','" + itemId+ "', '" + orderedQuantity+ "', '" + itemPrice+ "', '" + transactionId+ "','P')";
					stmt.executeUpdate(query);
					
					query = "INSERT INTO tbl_transacted_item (userId,itemId,itemQuantity,itemPrice, transactionId, transactionType) " +
							"VALUES ('" + sellerId+ "','" + itemId+ "', '" + orderedQuantity+ "', '" + itemPrice+ "', '" + transactionId+ "','S')";
					stmt.executeUpdate(query);
					
					query = "DELETE from tbl_cart_item WHERE cart_itemId = '" + cartItemId + "'";
					stmt.executeUpdate(query);
					
					query = "Update tbl_advertisement SET itemQuantity = '" + (availableQuantity - orderedQuantity)+"' WHERE itemId = '"+ itemId +"'";
					stmt.executeUpdate(query);
					
					result = "true";
				}
				
								
			}			
			
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	ArrayList<TransactedItem> getPurchasedItems(String userId) {
		String query = null;
		ArrayList<TransactedItem> alTransactedItem = new ArrayList<TransactedItem>();
		
		try {
			query = "SELECT * from tbl_transacted_item WHERE userId = '" + userId +"' and transactionType = 'P'";
			rs = stmt.executeQuery(query);
			
			while(rs.next()) {
				TransactedItem oTransactedItem = new TransactedItem();
				oTransactedItem.setItemId(rs.getInt("itemId"));
				System.out.println("Harry" + oTransactedItem.getItemId());
				oTransactedItem.setItemQuantity(rs.getInt("itemQuantity"));
				System.out.println("Harry" + oTransactedItem.getItemQuantity());
				oTransactedItem.setItemPrice(rs.getFloat("itemPrice"));
				oTransactedItem.setUserId(rs.getInt("userId"));
				alTransactedItem.add(oTransactedItem);				
			}			
			
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		
		return alTransactedItem;
	}
	
	ArrayList<TransactedItem> getSoldItems(String userId) {
		String query = null;
		ArrayList<TransactedItem> alTransactedItem = new ArrayList<TransactedItem>();
		
		try {
			query = "SELECT * from tbl_transacted_item WHERE userId = '" + userId +"' and transactionType = 'S'";
			rs = stmt.executeQuery(query);
			
			while(rs.next()) {
				TransactedItem oTransactedItem = new TransactedItem();
				oTransactedItem.setItemId(rs.getInt("itemId"));
				System.out.println("Harry" + oTransactedItem.getItemId());
				oTransactedItem.setItemQuantity(rs.getInt("itemQuantity"));
				System.out.println("Harry" + oTransactedItem.getItemQuantity());
				oTransactedItem.setItemPrice(rs.getFloat("itemPrice"));
				oTransactedItem.setUserId(rs.getInt("userId"));
				alTransactedItem.add(oTransactedItem);				
			}			
			
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		
		return alTransactedItem;
	}
	
	String updateMyAdvertisement(String itemId,String itemQuantity) {
		String result = "false";
		
		String query = "Update tbl_advertisement SET itemQuantity = '" + itemQuantity +"' WHERE itemId = '"+ itemId +"'";
		try {
			int rowCount = stmt.executeUpdate(query);
			if(rowCount == 1) {
				result = "true";
			}
		}	
		catch(SQLException e) {
			e.printStackTrace();
		}
		
		
		return result;
	}
	
	String deleteMyAdvertisement(String itemId) {
		
		String result = null;
		
		String query = "SELECT count(itemId) as count_item_id FROM tbl_transacted_item WHERE itemId = '" + itemId + "'";
		try {
			rs = stmt.executeQuery(query);
			rs.next();
			if(rs.getInt("count_item_id") > 0) {
				result = "false";
			}
			else {
				query = "DELETE from tbl_advertisement WHERE itemId = '" + itemId + "'";
				int rowCount = stmt.executeUpdate(query);
				if(rowCount == 1) {
					result="true";
				}
			}
		}	
		catch(SQLException e) {
			e.printStackTrace();
		}
		
		
		return result;
	}
	
}
